<?php

namespace app\store\controller;

use app\admin\common\Base;
use think\Db;
use app\admin\common\Excel;

//商品库存余额表
class Goodsbalance extends Base
{

    public function index()
    {
        $data = $this->request->param();

        if (isset($data['ajax']) && $data['ajax'] == 100) {
            $page = isset($data['page']) ? $data['page'] : 1;
            $page = intval($page);
            $limit = isset($data['rows']) ? $data['rows'] : 10;
            $limit = intval($limit);
            $start = $limit * ($page - 1);

            $list = Db::name('goods')->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->select();
            $count = Db::name('goods')->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->count();
            // 完善商品信息
            foreach ($list as $key => $value) {
                $list[$key]['goods_category_name'] = Db::name("goods_category")->where('id', $value['goods_category_id'])->value('name');
                $list[$key]['sub_unit'] = '';
                if ($value['is_multi_unit'] == 0) {
                    $list[$key]['unit_name'] = Db::name("unit")->where('id', $value['unit'])->value('name');
                } else {
                    $list[$key]['unit_name'] = Db::name("unit")->where('id', $value['multi_unit'])->value('name');
                    $list[$key]['sub_unit'] = $this->get_goods_sub_unit($value['multi_unit']);
                }
            }

            // 查询库存信息
            foreach ($list as $key => $value) {
                $innum = Db::name('stock_record_good')->alias('a')->join('stock_record b', 'a.pid = b.id')->where(['b.status' => 1, 'b.is_del' => 0])->where('a.is_del', 0)->where('a.o_type', 0)->where(['a.good_id' => $value['id']])->sum('a.number');
                $outnum = Db::name('stock_record_good')->alias('a')->join('stock_record b', 'a.pid = b.id')->where(['b.status' => 1, 'b.is_del' => 0])->where('a.is_del', 0)->where('a.o_type', 1)->where(['a.good_id' => $value['id']])->sum('a.number');
                $list[$key]['all_store']['basenum'] = $innum - $outnum;
                $in = Db::name('stock_record_good')->alias('a')->join('stock_record b', 'a.pid = b.id')->where(['b.status' => 1, 'b.is_del' => 0])->where('a.is_del', 0)->where('a.o_type', 0)->where(['a.good_id' => $value['id']])->select();
                $price = 0;
                foreach ($in as $k => $v) {
                    $price += $v['price'];
                }
                $list[$key]['all_store']['price'] = $price == 0 ? 0 : $price / count($in);
                $list[$key]['all_store']['name'] = '所有仓库';
                if (($innum - $outnum) != 0 && $price != 0) {
                    $list[$key]['all_store']['cost'] = ($innum - $outnum) * $price / count($in);
                } else {
                    $list[$key]['all_store']['cost'] = 0;
                }

                // 仓库
                $storeList = Db::name('store')->where('status', 0)->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->select();
                foreach ($storeList as $key1 => $value1) {
                    $innum = Db::name('stock_record_good')->alias('a')->join('stock_record b', 'a.pid = b.id')->where(['b.status' => 1, 'b.is_del' => 0])->where('a.is_del', 0)->where('a.o_type', 0)->where(['a.good_id' => $value['id'], 'a.store_id' => $value1['id']])->sum('a.number');
                    $outnum = Db::name('stock_record_good')->alias('a')->join('stock_record b', 'a.pid = b.id')->where(['b.status' => 1, 'b.is_del' => 0])->where('a.is_del', 0)->where('a.o_type', 1)->where(['a.good_id' => $value['id'], 'a.store_id' => $value1['id']])->sum('a.number');
                    $list[$key]['store' . $key1]['basenum'] = $innum - $outnum;

                    $in = Db::name('stock_record_good')->alias('a')->join('stock_record b', 'a.pid = b.id')->where(['b.status' => 1, 'b.is_del' => 0])->where('a.is_del', 0)->where('a.o_type', 0)->where(['a.good_id' => $value['id'], 'a.store_id' => $value1['id']])->select();
                    $price = 0;
                    foreach ($in as $k1 => $v1) {
                        $price += $v1['price'];
                    }
                    $list[$key]['store' . $key1]['price'] = $price == 0 ? 0 : $price / count($in);
                    $list[$key]['store' . $key1]['name'] = $value1['name'];
                    if (($innum - $outnum) != 0 && $price != 0) {
                        $list[$key]['store' . $key1]['cost'] = ($innum - $outnum) * $price / count($in);
                    } else {
                        $list[$key]['store' . $key1]['cost'] = 0;
                    }
                }
            }
            // halt($list);
            return $this->grid_data($list, $count, $page, $limit);
        }
        $store_list = Db::name('store')->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->where('status', 0)->select();

        $this->assign('store_list', $store_list);
        return $this->fetch();
    }

    // 获取商品副单位
    private function get_goods_sub_unit($id)
    {
    }


    public function add()
    {
        $data = $this->request->param();

        return $this->fetch();
    }
    public function export()
    {
        $data = $this->request->param();

        $arr = explode(" ", $data['rowid']);

        $title = [
            'sn' => '商品编号',
            'name' => '商品名称',
            'goods_category_id' => '商品类别',
            'model' => '规格型号',
            'mark' => '商品备注',
            'unit' => '单位',
            'is_multi_unit' => '副单位',
            'code' => '商品条码',
            'sy_number' => '基本数量',
            'sy_fdw' => '副单位数',
            'sy_djcb' => '单位成本',
            'sy_cb' => '成本',
            'xsj_number' => '基本数量',
            'xsj_fdw' => '副单位数',
            'xsj_djcb' => '单位成本',
            'xsj_cb' => '成本',
            'hgy_number' => '基本数量',
            'hgy_fdw' => '副单位数',
            'hgy_djcb' => '单位成本',
            'hgy_cb' => '成本',
        ];
        $ck = [
            'sn' => '商品编号',
            'name' => '商品名称',
            'goods_category_id' => '商品类别',
            'model' => '规格型号',
            'mark' => '商品备注',
            'unit' => '单位',
            'is_multi_unit' => '副单位',
            'code' => '商品条码',
            'sy_number' => '所有仓库',
            'sy_fdw' => '',
            'sy_djcb' => '',
            'sy_cb' => '',
            'xsj_number' => '',
            'xsj_fdw' => '',
            'xsj_djcb' => '',
            'xsj_cb' => '',
            'hgy_number' => '',
            'hgy_fdw' => '',
            'hgy_djcb' => '',
            'hgy_cb' => '',
        ];
        $arr_list = [];
        $store = Db::name('store')->where('status', 0)->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->select();
        for ($i = 0; $i < count($store); $i++) {
            if (empty($ck['xsj_number'])) {
                $ck['xsj_number'] = $store[$i]['name'];
                continue;
            }
            $ck['hgy_number'] = $store[$i]['name'];
        }
        $goods = db('goods')->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->where('id', 'in', $arr)->select();
        foreach ($goods as $key => &$value) {
            $store = Db('stock_record_good')->where(['is_del' => 0, 'enterprise_id' => $this->EnterpriseId])->where('good_id', 'in', $value['id'])->select();
            $value['sy_number'] = array_sum(array_column($store, 'use_ck'));
            $c = count(array_column($store, 'price'));
            $b = array_sum(array_column($store, 'price'));
            $value['sy_djcb'] = $c == '0' ? '0' : round($b / $c);
            $value['sy_cb'] = array_sum(array_column($store, 'use_ck'));
            $value['sy_fdw'] = '';
            $value['xsj_number'] = '';
            $value['xsj_fdw'] = '';
            $value['xsj_djcb'] = '';
            $value['xsj_cb'] = '';
            $value['hgy_number'] = '';
            $value['hgy_fdw'] = '';
            $value['hgy_djcb'] = '';
            $value['hgy_cb'] = '';
            array_push($arr_list, $value);
        }
        // array_multisort(array_column($arr_list,'id'), SORT_DESC, $arr_list);//按照ID大小排序
        // halt($arr_list);
        //添加标题到数组头部
        array_unshift($arr_list, $title);
        array_unshift($arr_list, $ck);
        $kong_arr = $title;
        foreach ($kong_arr as $ke1 => $va1) {
            $kong_arr[$ke1] = '';
        }
        for ($i = 0; $i < 3; $i++) {
            array_unshift($arr_list, $kong_arr);
        }
        //根据此参数获取数据中的值，要与表格标题键名对应
        $keys = [];
        foreach ($title as $k2 => $v2) {
            array_push($keys, $k2);
        }
        $merge = [];
        $options = [];
        $o_type = -1;
        $merge = [ //合并
            'start' => 4,
            'arr_zimu' => ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'],
            'keyword' => 'sn',
        ];
        $options = [
            'alignCenter' => ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T'], //居中
            'bold' => ['A1:T3'], //加粗
            'fontSize' => ['zimu' => ['A1'], 'size' => 20], //字体大小
        ];
        $title = [ //标题
            'title' => "商品库存余额表",
            'max_zimu' => "T",
            'number' => '3',
        ];
        $excel_export_title = "商品库存余额表_" . date('Y-m-d_His') . '.xlsx'; //文件名称
        $excel = new Excel();
        $excel->exportExcel($arr_list, $keys, $excel_export_title, $options, $o_type, $merge, $title);

        exit();
    }
}
